
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/03/2013 14:08:13
-- Generated from EDMX file: c:\users\wei\documents\visual studio 2012\Projects\DelonixRegiaHMSys\DelonixRegiaHMSys.Infrastructure\Data\DRHData.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_checkIn_guestID]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[checkIns] DROP CONSTRAINT [FK_checkIn_guestID];
GO
IF OBJECT_ID(N'[dbo].[FK_checkIn_reservationID]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[checkIns] DROP CONSTRAINT [FK_checkIn_reservationID];
GO
IF OBJECT_ID(N'[dbo].[FK_checkIn_roomNum]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[checkIns] DROP CONSTRAINT [FK_checkIn_roomNum];
GO
IF OBJECT_ID(N'[dbo].[FK_checkIn_userID]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[checkIns] DROP CONSTRAINT [FK_checkIn_userID];
GO
IF OBJECT_ID(N'[dbo].[FK_checkOut_checkinID]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[checkOuts] DROP CONSTRAINT [FK_checkOut_checkinID];
GO
IF OBJECT_ID(N'[dbo].[FK_checkOut_checkOut]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[checkOuts] DROP CONSTRAINT [FK_checkOut_checkOut];
GO
IF OBJECT_ID(N'[dbo].[FK_checkOut_invoiceID]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[checkOuts] DROP CONSTRAINT [FK_checkOut_invoiceID];
GO
IF OBJECT_ID(N'[dbo].[FK_checkOut_userID]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[checkOuts] DROP CONSTRAINT [FK_checkOut_userID];
GO
IF OBJECT_ID(N'[dbo].[FK_guestCreditCardDetails_guestID]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[guestCreditCardDetails] DROP CONSTRAINT [FK_guestCreditCardDetails_guestID];
GO
IF OBJECT_ID(N'[dbo].[FK_invoice_userID]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[invoices] DROP CONSTRAINT [FK_invoice_userID];
GO
IF OBJECT_ID(N'[dbo].[FK_roomReservation_guestID]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[roomReservations] DROP CONSTRAINT [FK_roomReservation_guestID];
GO
IF OBJECT_ID(N'[dbo].[FK_roomReservation_roomNum]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[roomReservations] DROP CONSTRAINT [FK_roomReservation_roomNum];
GO
IF OBJECT_ID(N'[dbo].[FK_roomReservation_userID]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[roomReservations] DROP CONSTRAINT [FK_roomReservation_userID];
GO
IF OBJECT_ID(N'[dbo].[FK_scheduleDetails_roomNum]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[scheduleDetails] DROP CONSTRAINT [FK_scheduleDetails_roomNum];
GO
IF OBJECT_ID(N'[dbo].[FK_scheduleDetails_staffID]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[scheduleDetails] DROP CONSTRAINT [FK_scheduleDetails_staffID];
GO
IF OBJECT_ID(N'[dbo].[FK_userAccount_staffID]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[userAccounts] DROP CONSTRAINT [FK_userAccount_staffID];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[checkIns]', 'U') IS NOT NULL
    DROP TABLE [dbo].[checkIns];
GO
IF OBJECT_ID(N'[dbo].[checkOuts]', 'U') IS NOT NULL
    DROP TABLE [dbo].[checkOuts];
GO
IF OBJECT_ID(N'[dbo].[guestCreditCardDetails]', 'U') IS NOT NULL
    DROP TABLE [dbo].[guestCreditCardDetails];
GO
IF OBJECT_ID(N'[dbo].[guestDetails]', 'U') IS NOT NULL
    DROP TABLE [dbo].[guestDetails];
GO
IF OBJECT_ID(N'[dbo].[invoices]', 'U') IS NOT NULL
    DROP TABLE [dbo].[invoices];
GO
IF OBJECT_ID(N'[dbo].[roomDetails]', 'U') IS NOT NULL
    DROP TABLE [dbo].[roomDetails];
GO
IF OBJECT_ID(N'[dbo].[roomReservations]', 'U') IS NOT NULL
    DROP TABLE [dbo].[roomReservations];
GO
IF OBJECT_ID(N'[dbo].[scheduleDetails]', 'U') IS NOT NULL
    DROP TABLE [dbo].[scheduleDetails];
GO
IF OBJECT_ID(N'[dbo].[staffDetails]', 'U') IS NOT NULL
    DROP TABLE [dbo].[staffDetails];
GO
IF OBJECT_ID(N'[dbo].[userAccounts]', 'U') IS NOT NULL
    DROP TABLE [dbo].[userAccounts];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'checkIns'
CREATE TABLE [dbo].[checkIns] (
    [checkinID] varchar(10)  NOT NULL,
    [guestID] varchar(10)  NOT NULL,
    [reservationID] varchar(10)  NULL,
    [userID] varchar(16)  NOT NULL,
    [numOfAdults] int  NOT NULL,
    [numOfChildren] int  NULL,
    [checkinDateTime] datetime  NOT NULL,
    [desiredCheckOutDateTime] datetime  NOT NULL,
    [roomType] varchar(20)  NOT NULL,
    [bedType] varchar(20)  NULL,
    [smokingOrNot] varchar(1)  NOT NULL,
    [addRemarks] varchar(225)  NULL,
    [lateCheckOutReq] varchar(1)  NOT NULL,
    [roomNum] int  NOT NULL,
    [paymentMode] varchar(11)  NOT NULL
);
GO

-- Creating table 'checkOuts'
CREATE TABLE [dbo].[checkOuts] (
    [checkOutID] varchar(10)  NOT NULL,
    [checkinID] varchar(10)  NOT NULL,
    [userID] varchar(16)  NOT NULL,
    [invoiceID] varchar(10)  NOT NULL
);
GO

-- Creating table 'guestCreditCardDetails'
CREATE TABLE [dbo].[guestCreditCardDetails] (
    [creditCardNum] varchar(16)  NOT NULL,
    [cardholderName] varchar(45)  NOT NULL,
    [guestID] varchar(10)  NOT NULL,
    [cardType] varchar(20)  NOT NULL,
    [cardExpirationDate] datetime  NOT NULL
);
GO

-- Creating table 'guestDetails'
CREATE TABLE [dbo].[guestDetails] (
    [guestID] varchar(10)  NOT NULL,
    [guestLastName] varchar(20)  NOT NULL,
    [guestFirstName] varchar(20)  NOT NULL,
    [homeAddress] varchar(72)  NOT NULL,
    [nationality] varchar(40)  NOT NULL,
    [contactNum] int  NOT NULL,
    [emailAddress] varchar(50)  NULL,
    [mailAddress] varchar(72)  NOT NULL
);
GO

-- Creating table 'invoices'
CREATE TABLE [dbo].[invoices] (
    [invoiceID] varchar(10)  NOT NULL,
    [userID] varchar(16)  NOT NULL,
    [checkOutDateTime] datetime  NOT NULL,
    [numberOfNightsStayed] int  NOT NULL,
    [addCosts] decimal(6,2)  NULL,
    [totalCost] decimal(6,2)  NOT NULL
);
GO

-- Creating table 'roomDetails'
CREATE TABLE [dbo].[roomDetails] (
    [roomNum] int  NOT NULL,
    [floorLevel] int  NOT NULL,
    [roomType] varchar(20)  NOT NULL,
    [bedType] varchar(20)  NOT NULL,
    [smokingOrNot] varchar(1)  NOT NULL,
    [maxOccupancy] int  NOT NULL,
    [roomStatus] varchar(3)  NOT NULL,
    [reservation] varchar(2)  NULL,
    [roomRates] decimal(6,2)  NOT NULL
);
GO

-- Creating table 'roomReservations'
CREATE TABLE [dbo].[roomReservations] (
    [reservationID] varchar(10)  NOT NULL,
    [guestID] varchar(10)  NOT NULL,
    [userID] varchar(16)  NOT NULL,
    [numOfAdults] int  NOT NULL,
    [numOfChildren] int  NULL,
    [arrivalDateTime] datetime  NOT NULL,
    [roomType] varchar(20)  NOT NULL,
    [bedType] varchar(20)  NULL,
    [smokingOrNot] varchar(1)  NOT NULL,
    [addRemarks] varchar(225)  NULL,
    [roomNum] int  NOT NULL,
    [reservationStatus] varchar(2)  NOT NULL
);
GO

-- Creating table 'scheduleDetails'
CREATE TABLE [dbo].[scheduleDetails] (
    [scheduleID] varchar(10)  NOT NULL,
    [roomNum] int  NOT NULL,
    [staffID] varchar(10)  NOT NULL,
    [scheduledDateTime] datetime  NOT NULL
);
GO

-- Creating table 'staffDetails'
CREATE TABLE [dbo].[staffDetails] (
    [staffID] varchar(10)  NOT NULL,
    [staffImage] varchar(20)  NOT NULL,
    [staffLastName] varchar(20)  NOT NULL,
    [staffFirstName] varchar(20)  NOT NULL,
    [DOB] datetime  NOT NULL,
    [bankAccNum] varchar(26)  NOT NULL,
    [homeAddress] varchar(72)  NOT NULL,
    [phoneNum] int  NOT NULL,
    [dutiesName] varchar(20)  NOT NULL
);
GO

-- Creating table 'userAccounts'
CREATE TABLE [dbo].[userAccounts] (
    [userID] varchar(16)  NOT NULL,
    [staffID] varchar(10)  NULL,
    [password] varchar(16)  NOT NULL,
    [accessLevel] varchar(2)  NOT NULL,
    [userStatus] varchar(3)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [checkinID] in table 'checkIns'
ALTER TABLE [dbo].[checkIns]
ADD CONSTRAINT [PK_checkIns]
    PRIMARY KEY CLUSTERED ([checkinID] ASC);
GO

-- Creating primary key on [checkOutID] in table 'checkOuts'
ALTER TABLE [dbo].[checkOuts]
ADD CONSTRAINT [PK_checkOuts]
    PRIMARY KEY CLUSTERED ([checkOutID] ASC);
GO

-- Creating primary key on [creditCardNum], [cardholderName] in table 'guestCreditCardDetails'
ALTER TABLE [dbo].[guestCreditCardDetails]
ADD CONSTRAINT [PK_guestCreditCardDetails]
    PRIMARY KEY CLUSTERED ([creditCardNum], [cardholderName] ASC);
GO

-- Creating primary key on [guestID] in table 'guestDetails'
ALTER TABLE [dbo].[guestDetails]
ADD CONSTRAINT [PK_guestDetails]
    PRIMARY KEY CLUSTERED ([guestID] ASC);
GO

-- Creating primary key on [invoiceID] in table 'invoices'
ALTER TABLE [dbo].[invoices]
ADD CONSTRAINT [PK_invoices]
    PRIMARY KEY CLUSTERED ([invoiceID] ASC);
GO

-- Creating primary key on [roomNum] in table 'roomDetails'
ALTER TABLE [dbo].[roomDetails]
ADD CONSTRAINT [PK_roomDetails]
    PRIMARY KEY CLUSTERED ([roomNum] ASC);
GO

-- Creating primary key on [reservationID] in table 'roomReservations'
ALTER TABLE [dbo].[roomReservations]
ADD CONSTRAINT [PK_roomReservations]
    PRIMARY KEY CLUSTERED ([reservationID] ASC);
GO

-- Creating primary key on [scheduleID] in table 'scheduleDetails'
ALTER TABLE [dbo].[scheduleDetails]
ADD CONSTRAINT [PK_scheduleDetails]
    PRIMARY KEY CLUSTERED ([scheduleID] ASC);
GO

-- Creating primary key on [staffID] in table 'staffDetails'
ALTER TABLE [dbo].[staffDetails]
ADD CONSTRAINT [PK_staffDetails]
    PRIMARY KEY CLUSTERED ([staffID] ASC);
GO

-- Creating primary key on [userID] in table 'userAccounts'
ALTER TABLE [dbo].[userAccounts]
ADD CONSTRAINT [PK_userAccounts]
    PRIMARY KEY CLUSTERED ([userID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [guestID] in table 'checkIns'
ALTER TABLE [dbo].[checkIns]
ADD CONSTRAINT [FK_checkIn_guestID]
    FOREIGN KEY ([guestID])
    REFERENCES [dbo].[guestDetails]
        ([guestID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_checkIn_guestID'
CREATE INDEX [IX_FK_checkIn_guestID]
ON [dbo].[checkIns]
    ([guestID]);
GO

-- Creating foreign key on [reservationID] in table 'checkIns'
ALTER TABLE [dbo].[checkIns]
ADD CONSTRAINT [FK_checkIn_reservationID]
    FOREIGN KEY ([reservationID])
    REFERENCES [dbo].[roomReservations]
        ([reservationID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_checkIn_reservationID'
CREATE INDEX [IX_FK_checkIn_reservationID]
ON [dbo].[checkIns]
    ([reservationID]);
GO

-- Creating foreign key on [roomNum] in table 'checkIns'
ALTER TABLE [dbo].[checkIns]
ADD CONSTRAINT [FK_checkIn_roomNum]
    FOREIGN KEY ([roomNum])
    REFERENCES [dbo].[roomDetails]
        ([roomNum])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_checkIn_roomNum'
CREATE INDEX [IX_FK_checkIn_roomNum]
ON [dbo].[checkIns]
    ([roomNum]);
GO

-- Creating foreign key on [userID] in table 'checkIns'
ALTER TABLE [dbo].[checkIns]
ADD CONSTRAINT [FK_checkIn_userID]
    FOREIGN KEY ([userID])
    REFERENCES [dbo].[userAccounts]
        ([userID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_checkIn_userID'
CREATE INDEX [IX_FK_checkIn_userID]
ON [dbo].[checkIns]
    ([userID]);
GO

-- Creating foreign key on [checkinID] in table 'checkOuts'
ALTER TABLE [dbo].[checkOuts]
ADD CONSTRAINT [FK_checkOut_checkinID]
    FOREIGN KEY ([checkinID])
    REFERENCES [dbo].[checkIns]
        ([checkinID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_checkOut_checkinID'
CREATE INDEX [IX_FK_checkOut_checkinID]
ON [dbo].[checkOuts]
    ([checkinID]);
GO

-- Creating foreign key on [checkOutID] in table 'checkOuts'
ALTER TABLE [dbo].[checkOuts]
ADD CONSTRAINT [FK_checkOut_checkOut]
    FOREIGN KEY ([checkOutID])
    REFERENCES [dbo].[checkOuts]
        ([checkOutID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [invoiceID] in table 'checkOuts'
ALTER TABLE [dbo].[checkOuts]
ADD CONSTRAINT [FK_checkOut_invoiceID]
    FOREIGN KEY ([invoiceID])
    REFERENCES [dbo].[invoices]
        ([invoiceID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_checkOut_invoiceID'
CREATE INDEX [IX_FK_checkOut_invoiceID]
ON [dbo].[checkOuts]
    ([invoiceID]);
GO

-- Creating foreign key on [userID] in table 'checkOuts'
ALTER TABLE [dbo].[checkOuts]
ADD CONSTRAINT [FK_checkOut_userID]
    FOREIGN KEY ([userID])
    REFERENCES [dbo].[userAccounts]
        ([userID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_checkOut_userID'
CREATE INDEX [IX_FK_checkOut_userID]
ON [dbo].[checkOuts]
    ([userID]);
GO

-- Creating foreign key on [guestID] in table 'guestCreditCardDetails'
ALTER TABLE [dbo].[guestCreditCardDetails]
ADD CONSTRAINT [FK_guestCreditCardDetails_guestID]
    FOREIGN KEY ([guestID])
    REFERENCES [dbo].[guestDetails]
        ([guestID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_guestCreditCardDetails_guestID'
CREATE INDEX [IX_FK_guestCreditCardDetails_guestID]
ON [dbo].[guestCreditCardDetails]
    ([guestID]);
GO

-- Creating foreign key on [guestID] in table 'roomReservations'
ALTER TABLE [dbo].[roomReservations]
ADD CONSTRAINT [FK_roomReservation_guestID]
    FOREIGN KEY ([guestID])
    REFERENCES [dbo].[guestDetails]
        ([guestID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_roomReservation_guestID'
CREATE INDEX [IX_FK_roomReservation_guestID]
ON [dbo].[roomReservations]
    ([guestID]);
GO

-- Creating foreign key on [userID] in table 'invoices'
ALTER TABLE [dbo].[invoices]
ADD CONSTRAINT [FK_invoice_userID]
    FOREIGN KEY ([userID])
    REFERENCES [dbo].[userAccounts]
        ([userID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_invoice_userID'
CREATE INDEX [IX_FK_invoice_userID]
ON [dbo].[invoices]
    ([userID]);
GO

-- Creating foreign key on [roomNum] in table 'roomReservations'
ALTER TABLE [dbo].[roomReservations]
ADD CONSTRAINT [FK_roomReservation_roomNum]
    FOREIGN KEY ([roomNum])
    REFERENCES [dbo].[roomDetails]
        ([roomNum])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_roomReservation_roomNum'
CREATE INDEX [IX_FK_roomReservation_roomNum]
ON [dbo].[roomReservations]
    ([roomNum]);
GO

-- Creating foreign key on [roomNum] in table 'scheduleDetails'
ALTER TABLE [dbo].[scheduleDetails]
ADD CONSTRAINT [FK_scheduleDetails_roomNum]
    FOREIGN KEY ([roomNum])
    REFERENCES [dbo].[roomDetails]
        ([roomNum])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_scheduleDetails_roomNum'
CREATE INDEX [IX_FK_scheduleDetails_roomNum]
ON [dbo].[scheduleDetails]
    ([roomNum]);
GO

-- Creating foreign key on [userID] in table 'roomReservations'
ALTER TABLE [dbo].[roomReservations]
ADD CONSTRAINT [FK_roomReservation_userID]
    FOREIGN KEY ([userID])
    REFERENCES [dbo].[userAccounts]
        ([userID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_roomReservation_userID'
CREATE INDEX [IX_FK_roomReservation_userID]
ON [dbo].[roomReservations]
    ([userID]);
GO

-- Creating foreign key on [staffID] in table 'scheduleDetails'
ALTER TABLE [dbo].[scheduleDetails]
ADD CONSTRAINT [FK_scheduleDetails_staffID]
    FOREIGN KEY ([staffID])
    REFERENCES [dbo].[staffDetails]
        ([staffID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_scheduleDetails_staffID'
CREATE INDEX [IX_FK_scheduleDetails_staffID]
ON [dbo].[scheduleDetails]
    ([staffID]);
GO

-- Creating foreign key on [staffID] in table 'userAccounts'
ALTER TABLE [dbo].[userAccounts]
ADD CONSTRAINT [FK_userAccount_staffID]
    FOREIGN KEY ([staffID])
    REFERENCES [dbo].[staffDetails]
        ([staffID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_userAccount_staffID'
CREATE INDEX [IX_FK_userAccount_staffID]
ON [dbo].[userAccounts]
    ([staffID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------